/**
 Ceated by Amit Mudgal (MicroStrategy Principal Designer) for GMS on 6/20/2014
 *
 */
package com.amex.SQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.amex.SQL.debug.DebugSQL;
import com.amex.uam.pojo.MS_PROJ;
import com.amex.uam.pojo.MS_USER_FUNC_GRP_ROLE;
import com.amex.uam.pojo.MS_USER_PROJ;
import com.amex.uam.pojo.OPT_BLUE_ACS_RSN;
import com.amex.uam.pojo.USER_DEPT;
import com.amex.uam.tasks.UserProfile;

public class DBEditProfileServiceHandler {
	
	private static String SQL_GET_USER_PROFILE = DebugSQL.DEBUG ? DebugSQL.SQL_GET_USER_PROFILE :
			"SELECT	A.USER_STA_CD,A.USER_TYPE_CD,A.USER_ADMIN_STA_CD,A.USER_ACSS_TYPE_CD,A.WORKFORCE_MBR_ID,A.USER_1ST_NM, I.FUNC_GRP_ROLE_ID,"
			+ "	A.USER_MI_TX,A.USER_LST_NM,	A.DEPT_ID,A.AMEX_BUS_CD,A.COST_CTR_CD,A.USER_CMPL_PHONE_NO,	A.USER_EMAIL_AD_TX,"
			+ "	A.WORKFORCE_MBR_MAN_1ST_NM,	A.WORKFORCE_MBR_MAN_MI_TX,	A.WORKFORCE_MBR_MAN_LST_NM,	A.WORKFORCE_MBR_MAN_EMAIL_AD_TX,"
			+ "	A.VP_NM,A.ACSS_RSN_TX,	DAYS_BETWEEN(CURRENT_TIMESTAMP,A.NDA_TS)+1 as DAYS_DIFF,DATE(A.SEC_XCPT_END_DT) as SEC_XCPT_END_DT,"
			+ "	DATE(A.NDA_TS) as NDA_TS,	A.AMEX_UNIT_CD,	D.DEPT_NM,E.AMEX_BUS_DS_TX,	F.AMEX_UNIT_DS_TX,G.FUNC_GRP_ROLE_NM,	A.CLM_GLBL_ACS_IN,"
			+ "	A.LIF_GLBL_ACS_IN"
			+ " FROM	"
			+ "		MS_USER_ADMIN A,"
			+ "		USER_DEPT D,"
			+ "		AMEX_BUSINESS E,"
			+ "		AMEX_UNIT F, "
			+ "		MS_USER_FUNC_GRP_ROLE I, "
			+ "		FUNC_GRP_ROLE G"
			+ " WHERE	"
			+ "		A.WORKFORCE_MBR_ID= I.WORKFORCE_MBR_ID "
			+ "		AND A.USER_ID=? "
			+ "		AND	A.DEPT_ID=D.DEPT_ID "
			+ "		AND	A.AMEX_BUS_CD=E.AMEX_BUS_CD "
			+ "		AND	A.AMEX_UNIT_CD=F.AMEX_UNIT_CD "
			+ "		AND I.FUNC_GRP_ROLE_ID = G.FUNC_GRP_ROLE_ID";
	//, MS_USR_CLM_ACS H
	//	AND A.WORKFORCE_MBR_ID=H.POA_SALE_SVC_ID

	private static String SQL_GET_WORKFORCE_MBR_ID = "select min(WORKFORCE_MBR_ID) as WORKFORCE_MBR_ID from MS_USER_ADMIN C where C.USER_ID = ?";
	
	private static String SQL_FUNCTIONAL_ROLE = "select A.FUNC_GRP_ROLE_ID, A.FUNC_GRP_ID, A.FUNC_GRP_ROLE_NM from FUNC_GRP_ROLE A";
	
	/*get WorkForce_MBR_ID*/
	public static String getWorkForceMBRId(String userID){
		if (userID == null || userID.equals("")){
			return null;
		}
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		String mbrId = null;

		if (conn == null){
			return null;
		} 
		try {
			pstat = conn.prepareStatement(SQL_GET_WORKFORCE_MBR_ID);
			pstat.setString(1, userID);
			res = pstat.executeQuery();
			while(res.next()){
				mbrId = Utils.trim(res.getString("WORKFORCE_MBR_ID"));
			}
			return mbrId;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			DBOperations.disconnect(conn, pstat, res);
		}
			
	}
	
	private static String SQL_GET_CLM_ID = "select A.POA_SALE_SERV_ID from MS_USER_CLM_ACS A where A.WORKFORCE_MBR_ID = ?";
	/*get CLM_ID*/
	public static String getCLMId(String userID) {
		
		String mbrID = getWorkForceMBRId(userID);
		
		if (mbrID == null || mbrID.equals("")){
			return null;
		}
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		String clmId = null;

		if (conn == null){
			return null;
		} 
		try {
			pstat = conn.prepareStatement(SQL_GET_CLM_ID);
			pstat.setString(1, mbrID);
			res = pstat.executeQuery();
			if(res.next()){
				clmId = Utils.trim(res.getString("POA_SALE_SERV_ID"), "");
			}
			if(clmId == null){
				return "";
			}
			return clmId;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	/*get function role table*/
	public static List<Map<String, String>> getFuncGRPRole(){
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null){
			return null;
		}
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			pstat = conn.prepareStatement(SQL_FUNCTIONAL_ROLE);
			res = pstat.executeQuery();
			while(res.next()){
				Map<String, String> map = new HashMap<String, String>();
				map.put("FUNC_GRP_ROLE_ID", ""+res.getInt("FUNC_GRP_ROLE_ID"));
				map.put("FUNC_GRP_ID", ""+res.getInt("FUNC_GRP_ID"));
				map.put("FUNC_GRP_ROLE_NM", Utils.trim(res.getString("FUNC_GRP_ROLE_NM")));
				list.add(map);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	
	public static Map getUserProfile(String userID) {
		
		if (userID == null || userID.equals("")) return null;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) return null;
		
		try {
			Map<String, Object> up = new HashMap<String, Object>();
			
			pstat = conn.prepareStatement(SQL_GET_USER_PROFILE);
			pstat.setString(1, userID);
			res = pstat.executeQuery();
			
			if (res.next()) {
				up.put("USER_STA_CD", Utils.trim(res.getString("USER_STA_CD")));
				up.put("USER_TYPE_CD", Utils.trim(res.getString("USER_TYPE_CD")));
				up.put("USER_ADMIN_STA_CD", Utils.trim(res.getString("USER_ADMIN_STA_CD")));
				
				up.put("eupMstrAccType", Utils.trim(res.getString("USER_ACSS_TYPE_CD")));
				up.put("eupAmexWebId", Utils.trim(res.getString("WORKFORCE_MBR_ID")));
				up.put("eupFstName", Utils.trim(res.getString("USER_1ST_NM")));
				up.put("eupMidName", Utils.trim(res.getString("USER_MI_TX")));
				up.put("eupLstName", Utils.trim(res.getString("USER_LST_NM")));
				up.put("eupDpartmntId", Utils.trim(res.getString("DEPT_ID")));
				up.put("eupBusnUCode", Utils.trim(res.getString("AMEX_BUS_CD")));
				up.put("eupCC", Utils.trim(res.getString("COST_CTR_CD")));
				up.put("eupPhone", Utils.trim(res.getString("USER_CMPL_PHONE_NO")));
				up.put("eupEmail", Utils.trim(res.getString("USER_EMAIL_AD_TX")));
				up.put("eupMFstName", Utils.trim(res.getString("WORKFORCE_MBR_MAN_1ST_NM")));
				up.put("eupMgrMidName", Utils.trim(res.getString("WORKFORCE_MBR_MAN_MI_TX")));
				up.put("eupMgrLstName", Utils.trim(res.getString("WORKFORCE_MBR_MAN_LST_NM")));
				up.put("eupMEmail", Utils.trim(res.getString("WORKFORCE_MBR_MAN_EMAIL_AD_TX")));
				up.put("eupVPName", Utils.trim(res.getString("VP_NM")));
				up.put("eupReason", Utils.trim(res.getString("ACSS_RSN_TX")));
				
				// up.put("MS_PROJ_NM", res.getString("MS_PROJ_NM").trim());
				up.put("DAYS_DIFF", res.getInt("DAYS_DIFF"));
				up.put("eupExpEndCd", res.getDate("SEC_XCPT_END_DT"));
				up.put("NDA_TS", res.getDate("NDA_TS"));
				
				up.put("eupAmexUCd", Utils.trim(res.getString("AMEX_UNIT_CD")));
				up.put("eupDpartmntDesc", Utils.trim(res.getString("DEPT_NM").trim()));
				up.put("eupBsnUCDesc", Utils.trim(res.getString("AMEX_BUS_DS_TX")));
				up.put("eupAmexUDesc", Utils.trim(res.getString("AMEX_UNIT_DS_TX"))); 
				
				up.put("FUNC_GRP_ROLE_NM", Utils.trim(res.getString("FUNC_GRP_ROLE_NM")));
				
				up.put("checkbox", Utils.trim(res.getString("CLM_GLBL_ACS_IN")));
				up.put("radio3", Utils.trim(res.getString("LIF_GLBL_ACS_IN"))); 
				up.put("FUNC_GRP_ROLE_ID", Utils.trim(res.getString("FUNC_GRP_ROLE_ID")));
				//up.put("eupClmID", res.getString("POA_SALE_SVC_ID"));
			}
			
			return up;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
		
	}

	private static String SQL_GET_ACS_RSN = "SELECT OPT_BLUE_ACS_RSN_CD,OPT_BLUE_ACS_RSN_DS FROM OPT_BLUE_ACS_RSN";
	public static List<Map<String, Object>> getAccessReasonList() {
		Connection conn = DBOperations.getConnection();
		Statement stat = null;
		ResultSet res = null;

		if (conn == null) return null;
		
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
			
			stat = conn.createStatement();
			res = stat.executeQuery(SQL_GET_ACS_RSN);
			
			while(res.next()) {
				Map<String, Object> up = new HashMap<String, Object>();
				up.put("OPT_BLUE_ACS_RSN_CD", res.getInt("OPT_BLUE_ACS_RSN_CD"));
				up.put("OPT_BLUE_ACS_RSN_DS", Utils.trim(res.getString("OPT_BLUE_ACS_RSN_DS")));
				list.add(up);
			}
			
			return list;
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, stat, res);
		}
		
	}
	
	private static final String SQL_DELETE_USER_OPT_BLUE = "DELETE FROM MS_USR_OPT_BLUE_ACS_RSN WHERE WORKFORCE_MBR_ID=?";
	
	public static boolean deleteUserOptBlue(String amexWebId){
		if (amexWebId == null || amexWebId.trim().equals("")) {
			return false;
		}
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_DELETE_USER_OPT_BLUE);
			int rowCount = pstat.executeUpdate();
			
			return rowCount > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static final String SQL_INSERT_USER_OPT_BLUE = "INSERT INTO MS_USR_OPT_BLUE_ACS_RSN"
			+ " (WORKFORCE_MBR_ID, OPT_BLUE_ACS_RSN_CD, CREAT_TS, OPT_BLUE_OTHER_ACS_RSN_DS)"
			+ "	VALUES (?,?,CURRENT_TIMESTAMP,?)";

	public static boolean insertUserOptBlue(Map<String, Object> params) {
		if (params == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if(conn == null) return false;
	
		try {
			pstat = conn.prepareStatement(SQL_INSERT_USER_OPT_BLUE);
			pstat.setString(1, (String) (params.get("WORKFORCE_MBR_ID")));
			pstat.setInt(2, Utils.parseInt((String) (params.get("OPT_BLUE_ACS_RSN_CD")), 0));
			pstat.setString(3, (String) (params.get("OPT_BLUE_OTHER_ACS_RSN_DS")));
			
			int count = pstat.executeUpdate();
			
			return count > 0;
			
		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	
	private static final String SQL_FUNC_GRP = "SELECT FUNC_GRP_ID,FUNC_GRP_NM FROM FUNC_GRP";
	
	public static List<Map<String, String>> getFunctionalGroups() {
		
		Connection conn = DBOperations.getConnection();
		Statement stat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			List<Map<String, String>> list = new ArrayList<Map<String,String>>();
			
			stat = conn.createStatement();
			res = stat.executeQuery(SQL_FUNC_GRP);
			
			while (res.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("radio", Utils.trim(res.getString("FUNC_GRP_ID")));
				map.put("radioName", Utils.trim(res.getString("FUNC_GRP_NM")));
				list.add(map);
			}

			return list;
			
		}catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, stat, res);
		}
		
	}
	
	private static final String SQL_GET_USER_SEC_GRP_N = "SELECT SEC_GRP_NM FROM MS_USER_SEC_GRP WHERE SEC_GRP_XCPT_IN='N' AND WORKFORCE_MBR_ID=?";
	
	public static List<String> getSecGrp(String amexWebId) {
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			pstat = conn.prepareStatement(SQL_GET_USER_SEC_GRP_N);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();

			List<String> list = new ArrayList<String>();
			
			while (res.next()) {
				list.add( Utils.trim(res.getString("SEC_GRP_NM")));
				
			}
			return list;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
		
		
	}
	private static String SQL_GET_USER_SEC_GRP_Y = "SELECT SEC_GRP_NM FROM MS_USER_SEC_GRP WHERE SEC_GRP_XCPT_IN='Y' and WORKFORCE_MBR_ID=?";
	
	public static List<String> getUserSecGrp(String amexWebId) {
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null)
			return null;

		try {

			pstat = conn.prepareStatement(SQL_GET_USER_SEC_GRP_Y);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();

			List<String> list = new ArrayList<String>();

			while (res.next()) {
				list.add(Utils.trim(res.getString("SEC_GRP_NM")));
			}

			return list;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}

	}
	
	private static final String SQL_GET_GEO_ACSS_LVL_CD = "SELECT GEO_ACSS_LVL_CD FROM MS_USER_ADMIN WHERE WORKFORCE_MBR_ID=?";
	
	public static List<String> getGeoAcssLvlCd(String amexWebId) {
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			pstat = conn.prepareStatement(SQL_GET_GEO_ACSS_LVL_CD);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();
			List<String> list = new ArrayList<String>();
			while(res.next()) {
				list.add( Utils.trim(res.getString("GEO_ACSS_LVL_CD")));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
		
	}
	
	private static final String SQL_GET_USER_RGN_ACSS = "SELECT RGN_ABBR_NM FROM MS_USER_RGN_ACSS WHERE WORKFORCE_MBR_ID=?";
	
	public static List<String> getUserRgnAcss(String amexWebId) {
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			pstat = conn.prepareStatement(SQL_GET_USER_RGN_ACSS);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();
			List<String> list = new ArrayList<String>();
			while(res.next()) {
				list.add( Utils.trim(res.getString("RGN_ABBR_NM")));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
			
	private static final String SQL_GET_CTRY_NM = "SELECT CTRY_NM FROM MS_USER_CTRY_ACSS WHERE WORKFORCE_MBR_ID=?";
	
	public static List<String> getCtryNm(String amexWebId) {
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		

		try {
			pstat = conn.prepareStatement(SQL_GET_USER_RGN_ACSS);
			pstat.setString(1, amexWebId);
			res = pstat.executeQuery();
			List<String> list = new ArrayList<String>();
			while(res.next()) {
				list.add( Utils.trim(res.getString("CTRY_NM")));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	
	private static final String SQL_UPDATE_USER_PROFILE = "Update MS_USER_ADMIN "
			+ " SET LST_UPDT_TS=CURRENT_TIMESTAMP, "
			+ " COST_CTR_CD=?, "
			+ " WORKFORCE_MBR_MAN_1ST_NM=?, "
			+ " WORKFORCE_MBR_MAN_MI_TX=?,"
			+ "	WORKFORCE_MBR_MAN_LST_NM=?,"
			+ " WORKFORCE_MBR_MAN_EMAIL_AD_TX=?,"
			+ " USER_CMPL_PHONE_NO=?,"
			+ " DEPT_ID=? "
			+ " WHERE WORKFORCE_MBR_ID=?";
	
	public static boolean updateUserProfile(UserProfile up) {
		
		if(up == null) return false;
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if(conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_UPDATE_USER_PROFILE);
			
			pstat.setString(1, up.getCOST_CTR_CD());
			pstat.setString(2, up.getWORKFORCE_MBR_MAN_1ST_NM());
			pstat.setString(3, up.getWORKFORCE_MBR_MAN_MI_TX());
			pstat.setString(4, up.getWORKFORCE_MBR_MAN_LST_NM());
			pstat.setString(5, up.getWORKFORCE_MBR_MAN_EMAIL_AD_TX());
			pstat.setString(6, up.getUSER_CMPL_PHONE_NO());
			pstat.setString(7, up.getDEPT_ID());
		
			pstat.setString(8, up.getWORKFORCE_MBR_ID());
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static final String SQL_EDIT_USER_ROLE_ID = "update ms_user_func_grp_role set func_grp_role_id = ? where workforce_mbr_id = ?";
	
	public static boolean editUserRoleID(MS_USER_FUNC_GRP_ROLE bean) {
		if(bean == null) return false;
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if(conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_EDIT_USER_ROLE_ID);
			
			pstat.setInt(1, bean.getFUNC_GRP_ROLE_ID());
			pstat.setString(2, bean.getWORKFORCE_MBR_ID());
		
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static final String SQL_GET_DEPT_ID = "SELECT DEPT_ID FROM USER_DEPT WHERE DEPT_NM=?";
	
	public static String getDeptID(String deptName) {

		if (deptName == null) return null;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) return null;

		try {
			pstat = conn.prepareStatement(SQL_GET_DEPT_ID);
			pstat.setString(1, deptName);
			res = pstat.executeQuery();

			String deptID = null;

			if (res.next()) {
				deptID = res.getString("DEPT_ID");
			}

			return deptID;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	private static final String SQL_GET_DEPT_ID_BY_ID = "SELECT DEPT_NM FROM USER_DEPT WHERE DEPT_ID=?";
	
	public static String getDeptName(String deptID) {

		if (deptID == null) return null;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;

		if (conn == null) return null;

		try {
			pstat = conn.prepareStatement(SQL_GET_DEPT_ID);
			pstat.setString(1, deptID);
			res = pstat.executeQuery();

			String deptName = null;

			if (res.next()) {
				deptName = res.getString("DEPT_NM");
			}

			return deptName;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	private static final String SQL_ADD_DEPT = "INSERT INTO USER_DEPT(DEPT_ID, DEPT_NM) VALUES(?,?)";
	
	public static boolean addDeptment(USER_DEPT bean) {
		if (bean == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_ADD_DEPT);
			
			pstat.setString(1, bean.getDEPT_ID());
			pstat.setString(2, bean.getDEPT_NM());
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static final String SQL_DEL_ACCESS_REASON = "DELETE FROM MS_USR_OPT_BLUE_ACS_RSN WHERE WORKFORCE_MBR_ID=?";
	
	public static boolean deleteAccessReason(String WORKFORCE_MBR_ID) {
		
		if (WORKFORCE_MBR_ID == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_DEL_ACCESS_REASON);
			
			pstat.setString(1, WORKFORCE_MBR_ID);
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static final String SQL_ADD_ACCESS_REASON = "INSERT INTO MS_USR_OPT_BLUE_ACS_RSN (WORKFORCE_MBR_ID, OPT_BLUE_ACS_RSN_CD, CREAT_TS, OPT_BLUE_OTHER_ACS_RSN_DS)VALUES (?,?,?,?)";
	
	public static boolean addAccessReason(OPT_BLUE_ACS_RSN bean) {
		
		if (bean == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_ADD_ACCESS_REASON);
			
			pstat.setString(1, bean.getWORKFORCE_MBR_ID());
			pstat.setInt(2, bean.getOPT_BLUE_ACS_RSN_CD());
			pstat.setTimestamp(3, bean.getCREATE_TS());
			pstat.setString(4, bean.getOPT_BLUE_OTHER_ACS_RSN_DS());
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
	}
	
	private static String SQL_GET_USER_ROLE_ID = "SELECT R.FUNC_GRP_ROLE_ID FROM MS_USER_FUNC_GRP_ROLE R, MS_USER_ADMIN A WHERE A.USER_ID = ? AND A.WORKFORCE_MBR_ID=R.WORKFORCE_MBR_ID";
	
	public static int getUserRoleID(String userID) {
		if (userID == null) return -1;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if (conn == null) return -1;
		
		try {
			pstat = conn.prepareStatement(SQL_GET_USER_ROLE_ID);
			pstat.setString(1, userID);
			res = pstat.executeQuery();
			int roleId = -1;
			if (res.next()) {
				roleId = res.getInt("FUNC_GRP_ROLE_ID");
			}
			return roleId;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
		
	}
	
	private static String SQL_ADD_USER_PROJ = "Insert into ms_user_proj (workforce_mbr_id, ms_proj_id, creat_ts, role_ds_tx, role_creat_ts, role_updt_ts) values (?, ?, ?, ?, ?, ?)";
	
	public static boolean addUserProj(MS_USER_PROJ bean) {
		
		if (bean == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_ADD_USER_PROJ);
			
			pstat.setString(1, bean.getWORKFORCE_MBR_ID());
			pstat.setInt(2, bean.getMS_PROJ_ID());
			pstat.setTimestamp(3, bean.getCREAT_TS());
			pstat.setString(4, bean.getROLE_DS_TX());
			pstat.setTimestamp(5, bean.getROLE_CREAT_TS());
			pstat.setTimestamp(6, bean.getROLE_UPDT_TS());
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
	}
	
	private static String SQL_DELETE_USER_PROJ = "DELETE MS_USER_PROJ WHERE WORKFORCE_MBR_ID=? AND MS_PROJ_ID=?";
	
	public static boolean deleteUserProj(MS_USER_PROJ bean) { 
		if (bean == null) return false;
		
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		
		if (conn == null) return false;
		
		try {
			pstat = conn.prepareStatement(SQL_DELETE_USER_PROJ);
			
			pstat.setString(1, bean.getWORKFORCE_MBR_ID());
			pstat.setInt(2, bean.getMS_PROJ_ID());
			
			int rowCount = pstat.executeUpdate();
			return (rowCount > 0);
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			DBOperations.disconnect(conn, pstat);
		}
		
		
	}
	
	//private static String SQL_GET_USER_PROJ = "select workforce_mbr_id, ms_proj_id, creat_ts, role_ds_tx, role_creat_ts, role_updt_ts from ms_user_proj where workforce_mbr_id = ?";

	private static String SQL_GET_USER_PROJ_BY_USER_ID = "SELECT distinct	C.MS_PROJ_ID, C.MS_PROJ_NM FROM	MS_USER_ADMIN A, MS_USER_PROJ B, MS_PROJ C WHERE A.WORKFORCE_MBR_ID=B.WORKFORCE_MBR_ID AND B.MS_PROJ_ID=C.MS_PROJ_ID AND A.USER_ID=?";
	public static List<MS_USER_PROJ> getUserProjByUserID(String userId) {
		return getUserProjByID(userId, SQL_GET_USER_PROJ_BY_USER_ID);
	}
	
	private static String SQL_GET_USER_PROJ_BY_MEMBER_ID = "SELECT	 distinct C.MS_PROJ_ID, C.MS_PROJ_NM FROM	MS_USER_ADMIN A, MS_USER_PROJ B, MS_PROJ C WHERE A.WORKFORCE_MBR_ID=B.WORKFORCE_MBR_ID AND B.MS_PROJ_ID=C.MS_PROJ_ID AND A.WORKFORCE_MBR_ID=?";
	public static List<MS_USER_PROJ> getUserProjByMemberID(String amexWebId) {
		return getUserProjByID(amexWebId, SQL_GET_USER_PROJ_BY_MEMBER_ID);
	}
	
	public static List<MS_USER_PROJ> getUserProjByID(String id, String sql) {
		Connection conn = DBOperations.getConnection();
		PreparedStatement pstat = null;
		ResultSet res = null;
		
		if(conn == null) return null;
		
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, id);
			res = pstat.executeQuery();
			
			List<MS_USER_PROJ> list = new ArrayList<MS_USER_PROJ>();
			while(res.next()) {
				
				MS_USER_PROJ bean = new MS_USER_PROJ(); 
				bean.setMS_PROJ_ID(res.getInt("MS_PROJ_ID"));
				bean.setMS_PROJ_NM(Utils.trim(res.getString("MS_PROJ_NM")));
				list.add(bean);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBOperations.disconnect(conn, pstat, res);
		}
	}
	
	/* Get all projects */
	private static final String SQL_GET_ALL_MS_PROJ = "SELECT MS_PROJ_ID ,MS_PROJ_NM ,CREAT_TS FROM MS_PROJ";
	public static List<MS_PROJ> getAllProjects() {
		Connection conn = DBOperations.getConnection();
		Statement stat = null;
		ResultSet res = null;
		List<MS_PROJ> list = new ArrayList<MS_PROJ>(); 
		
		if (conn == null) return list;
		
		try {
			stat = conn.createStatement();
			res = stat.executeQuery(SQL_GET_ALL_MS_PROJ);
			while(res.next()) {
				MS_PROJ proj = new MS_PROJ();
				proj.setMS_PROJ_ID(res.getInt("MS_PROJ_ID"));
				proj.setMS_PROJ_NM(Utils.trim(res.getString("MS_PROJ_NM")));
				proj.setCREAT_TS(res.getTimestamp("CREAT_TS"));
				list.add(proj);
			}
		} catch (Throwable t) {
			t.printStackTrace();
		} finally {
			DBOperations.disconnect(conn, stat, res);
		}
		
		return list;
	}
	
	
	public static void main(String args[]){
		String userId = "8165840";
		String amexWebId = "rlavu";
		System.out.println(getUserProfile("8165840"));
		
		//System.out.println(getAccessReasonList());
		//System.out.println(getFunctionalGroups());
		//System.out.println(getSecGrp(amexWebId));
		//System.out.println(getUserSecGrp(amexWebId));
		//System.out.println(getGeoAcssLvlCd(amexWebId));
		//System.out.println(getUserRgnAcss(amexWebId));
		//System.out.println(getFuncGRPRole());
		
		//System.out.println(getUserRoleID(userId));
		//System.out.println(getUserProj(amexWebId));
		
		System.out.println(Utils.toJSON(getAllProjects()));
	}

}
